package com.ssm.jdbc;

import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.*;
import java.util.function.BiFunction;
import java.util.stream.Collectors;

import static com.ssm.jdbc.DateUtil.YYYY_MM_DD_HH_MM_SS;

public class DelOrderTask {

    public static void main(String[] args) {

        List<DbConfig> dbConfigs = dbConfigList();
        List<DbDataSource> dbConnList = new ArrayList<>();
        for (int i = 0; i < dbConfigs.size(); i++) {
            try {
                DbConfig item = dbConfigs.get(i);
                Connection connection = JdbcUtil.getConnection(item.getUrl(), item.getUser(), item.getPassword());
                DbDataSource dbConn = new DbDataSource(connection, "linear_btc_" + (i + 1));
                dbConn.setLimit(5000);
                dbConnList.add(dbConn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        String maxCreateDate = "2023-02-01";
        if (args != null && args.length > 0) {
            maxCreateDate = args[0];
        }
        ExecutorService executorService = Executors.newFixedThreadPool(16);
        CountDownLatch countDownLatch = new CountDownLatch(dbConnList.size());
        String finalMaxCreateDate = maxCreateDate;

        //Map<String, Long> lastIdMap = new ConcurrentHashMap<>();
        dbConnList.forEach(dbConn -> {
            executorService.submit(() -> {
                Long minId = 0L;
                Connection conn = dbConn.getConnection();
                String connName = dbConn.getConnName();
                Integer limit = dbConn.getLimit();
                try {
                    for (int i = 0; true; i++) {
                        long startTime = System.currentTimeMillis();
                        String querySql = "select id from t_order where id > " + minId + " and `create_date` < '" + finalMaxCreateDate + "' order by id asc limit " + limit;
                        List<Order> orderList = JdbcUtil.query(querySql, conn, Order.class, (rs, obj) -> {
                            List<Order> oList = new ArrayList<>();
                            ResultSet resultSet = (ResultSet) rs;
                            try {
                                while (resultSet.next()) {
                                    Order order = new Order();
                                    order.setId(resultSet.getLong(1));
                                    oList.add(order);
                                }
                            } catch (Throwable t) {
                                t.printStackTrace();
                            } finally {
                                JdbcUtil.release(resultSet, null, null);
                            }
                            return oList;
                        });
                        if (CollectionUtils.isEmpty(orderList)) {
                            break;
                        } else {
                            int idx = orderList.size() - 1;
                            minId = orderList.get(idx).getId();
                            if (i % 1000 == 0) {
                                sout("删除数据, 参数信息 id:" + minId + ",limit:" + limit + ",date:" + finalMaxCreateDate + ",size:" + orderList.size() + ".");
                            }
                            /*
                            if (Objects.equals(lastIdMap.getOrDefault(connName, Long.MIN_VALUE), minId)) {
                                // 删除完毕，两次的 minId 相同
                                break;
                            }
                            lastIdMap.put(connName, minId);
                             */
                        }
                        String ids = orderList.stream().map(o -> String.valueOf(o.getId())).collect(Collectors.joining(","));
                        String executeSql = "delete from t_order where id in (" + ids + ") and state in (5,6,7)";
                        JdbcUtil.execute(executeSql, conn);
                        Long cost = System.currentTimeMillis() - startTime;
                        if (cost > 500) {
                            sout("删除数据, 执行耗时 db: " + connName + ",id:" + minId + ",limit:" + limit + ",size:" + orderList.size() + ",cost:" + cost + ".");
                            sout("删除数据, 执行耗时 db: " + connName + ",cost:" + cost + ",sql:" + querySql + ".");
                        }
                        Thread.sleep(5);

                        if (i >= Integer.MAX_VALUE - 1) {
                            i = 0;
                        }
                    }
                } catch (Throwable t) {
                    t.printStackTrace();
                    System.err.println("发生异常，异常信息: " + t.getMessage());
                } finally {
                    countDownLatch.countDown();
                }
                System.out.println("删除完毕, dbName:" + connName);
            });
        });
        try {
            countDownLatch.await();
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
        dbConnList.forEach(conn -> JdbcUtil.release(null, null, conn.getConnection()));
        System.out.println("所有的数据删除完毕");
    }

    private static List<DbConfig> dbConfigList() {
        List<DbConfig> dbConfigList = new ArrayList<>();

        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3308.hwc-bj.huobiidc.com:3308/linear_btc1", "linear_btc1_usrc", "t6aBu4z_R03LpPdx"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3309.hwc-bj.huobiidc.com:3309/linear_btc2", "linear_btc2_usrc", "t7gLgqM_ES2EdTKT"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3310.hwc-bj.huobiidc.com:3310/linear_btc3", "linear_btc3_usrc", "y1zHVMBwmeM_nzJL"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3311.hwc-bj.huobiidc.com:3311/linear_btc4", "linear_btc4_usrc", "z4kSeA_k7VR5yfPH"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3312.hwc-bj.huobiidc.com:3312/linear_btc5", "linear_btc5_usrc", "z_3aMLZErQCbVQfe"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3313.hwc-bj.huobiidc.com:3313/linear_btc6", "linear_btc6_usrc", "n7vK6LQn9_qv3C1C"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3314.hwc-bj.huobiidc.com:3314/linear_btc7", "linear_btc7_usrc", "S4_tDwW0VPdrPY3B"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3315.hwc-bj.huobiidc.com:3315/linear_btc8", "linear_btc8_usrc", "e_4uDPSnzDVNCQxT"));
        dbConfigList.add(new DbConfig("jdbc:mysql://master-loadtest-3316.hwc-bj.huobiidc.com:3316/linear_btc9", "linear_btc9_usrc", "T2uE_VLFke5SpEzu"));
        return dbConfigList;
    }

    private static void sout(String str) {
        System.out.println(DateUtil.formatBySimpleDateFormat(new java.util.Date(), YYYY_MM_DD_HH_MM_SS) + ":" + str);
    }
}

class Order {
    private Long id;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

class DbDataSource {
    private Connection connection;
    private String connName;
    private Integer limit = 1000;

    public DbDataSource() {
    }

    public DbDataSource(Connection connection, String connName) {
        this.connection = connection;
        this.connName = connName;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public String getConnName() {
        return connName;
    }

    public void setConnName(String connName) {
        this.connName = connName;
    }

    public Integer getLimit() {
        return limit;
    }

    public void setLimit(Integer limit) {
        this.limit = limit;
    }
}


class DbConfig {

    private String url;
    private String user;
    private String password;

    public DbConfig(String url, String user, String password) {
        this.url = url;
        this.user = user;
        this.password = password;
    }

    public DbConfig() {
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

class JdbcUtil {

    private static final String[] MYSQL_DRRVERS = new String[]{"com.mysql.jdbc.Driver"};

    static {
        try {
            for (String MYSQL_DRRVER : MYSQL_DRRVERS) {
                Class.forName(MYSQL_DRRVER);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static <T> List<T> query(String querySql, Connection connection, Class<T> clazz) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(querySql);
        //todo 参数列表
        ResultSet resultSet = ps.executeQuery();
        return ResultSetPropertiesSimplifyHelps.putResult(resultSet, clazz);
    }

    public static <T> List<T> query(String querySql, Connection connection, Class<T> clazz, BiFunction function) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(querySql);
        //todo 参数列表
        ResultSet resultSet = ps.executeQuery();
        return (List<T>) function.apply(resultSet, clazz);
    }

    public static void execute(String executeSql, Connection connection) throws SQLException {
        Statement st = connection.createStatement();
        st.execute(executeSql);
        release(null, st, null);
    }


    /**
     * 释放资源
     *
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void release(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection(String url, String user, String password) throws SQLException {
        final Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
}


/**
 * ResultSet结果通过java反射赋值给java对象
 */
class ResultSetPropertiesSimplifyHelps {

    /**
     * 把ResultSet的结果放到java对象中
     *
     * @param <T>
     * @param rs  ResultSet
     * @param obj java类的class
     * @return
     */
    public static <T> ArrayList<T> putResult(ResultSet rs, Class<T> obj) {
        try {
            ArrayList<T> arrayList = new ArrayList<T>();
            ResultSetMetaData metaData = rs.getMetaData();
            /**
             * 获取总列数
             */
            int count = metaData.getColumnCount();
            while (rs.next()) {
                /**
                 * 创建对象实例
                 */
                T newInstance = obj.newInstance();
                for (int i = 1; i <= count; i++) {
                    /**
                     * 给对象的某个属性赋值
                     */
                    String name = metaData.getColumnName(i).toLowerCase();
                    name = toJavaField(name);// 改变列名格式成java命名格式
                    String substring = name.substring(0, 1);// 首字母大写
                    String replace = name.replaceFirst(substring, substring.toUpperCase());
                    Class<?> type = null;
                    try {
                        type = obj.getDeclaredField(name).getType();// 获取字段类型
                    } catch (NoSuchFieldException e) { // Class对象未定义该字段时,跳过
                        continue;
                    }

                    Method method = obj.getMethod("set" + replace, type);
                    /**
                     * 判断读取数据的类型
                     */
                    if (type.isAssignableFrom(String.class)) {
                        method.invoke(newInstance, rs.getString(i));
                    } else if (type.isAssignableFrom(byte.class) || type.isAssignableFrom(Byte.class)) {
                        method.invoke(newInstance, rs.getByte(i));// byte 数据类型是8位、有符号的，以二进制补码表示的整数
                    } else if (type.isAssignableFrom(short.class) || type.isAssignableFrom(Short.class)) {
                        method.invoke(newInstance, rs.getShort(i));// short 数据类型是 16 位、有符号的以二进制补码表示的整数
                    } else if (type.isAssignableFrom(int.class) || type.isAssignableFrom(Integer.class)) {
                        method.invoke(newInstance, rs.getInt(i));// int 数据类型是32位、有符号的以二进制补码表示的整数
                    } else if (type.isAssignableFrom(long.class) || type.isAssignableFrom(Long.class)) {
                        method.invoke(newInstance, rs.getLong(i));// long 数据类型是 64 位、有符号的以二进制补码表示的整数
                    } else if (type.isAssignableFrom(float.class) || type.isAssignableFrom(Float.class)) {
                        method.invoke(newInstance, rs.getFloat(i));// float 数据类型是单精度、32位、符合IEEE 754标准的浮点数
                    } else if (type.isAssignableFrom(double.class) || type.isAssignableFrom(Double.class)) {
                        method.invoke(newInstance, rs.getDouble(i));// double 数据类型是双精度、64 位、符合IEEE 754标准的浮点数
                    } else if (type.isAssignableFrom(BigDecimal.class)) {
                        method.invoke(newInstance, rs.getBigDecimal(i));
                    } else if (type.isAssignableFrom(boolean.class) || type.isAssignableFrom(Boolean.class)) {
                        method.invoke(newInstance, rs.getBoolean(i));// boolean数据类型表示一位的信息
                    } else if (type.isAssignableFrom(Date.class)) {
                        method.invoke(newInstance, rs.getDate(i));
                    }
                }
                arrayList.add(newInstance);
            }
            return arrayList;

        } catch (InstantiationException | IllegalAccessException | SQLException | SecurityException |
                 NoSuchMethodException | IllegalArgumentException | InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtil.release(rs, null, null);
        }

        return null;
    }

    /**
     * 数据库命名格式转java命名格式
     *
     * @param str 数据库字段名
     * @return java字段名
     */
    public static String toJavaField(String str) {

        String[] split = str.split("_");
        StringBuilder builder = new StringBuilder();
        builder.append(split[0]);// 拼接第一个字符

        // 如果数组不止一个单词
        if (split.length > 1) {
            for (int i = 1; i < split.length; i++) {
                // 去掉下划线，首字母变为大写
                String string = split[i];
                String substring = string.substring(0, 1);
                split[i] = string.replaceFirst(substring, substring.toUpperCase());
                builder.append(split[i]);
            }
        }

        return builder.toString();
    }

}


class DateUtil {

    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
    private static final ThreadLocal<SimpleDateFormat> threadLocalDateFormat = ThreadLocal.withInitial(() -> new SimpleDateFormat(YYYY_MM_DD_HH_MM_SS, Locale.US));
    private static final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(YYYY_MM_DD_HH_MM_SS);

    /**
     * SimpleDateFormat要结合ThreadLocal使用,避免并发问题
     */
    public static String formatBySimpleDateFormat(java.util.Date date, String... pattern) {
        SimpleDateFormat simpleDateFormat = threadLocalDateFormat.get();
        if (pattern.length > 0) {
            simpleDateFormat.applyPattern(pattern[0]);
        }
        return simpleDateFormat.format(date);
    }


    /**
     * java8新增的方法
     */
    public static String formatByDateTimeFormatter(java.util.Date date, String... pattern) {
        LocalDateTime localDateTime = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
        if (pattern.length > 0) {
            return DateTimeFormatter.ofPattern(pattern[0]).withZone(ZoneId.systemDefault()).format(localDateTime);
        }
        return dateTimeFormatter.format(localDateTime);
    }

}